\c pincaicai postgres;

-- =========================
-- Updated inventory management functions
-- =========================

-- 1. Generic stock check function
CREATE OR REPLACE FUNCTION check_product_stock(category_param VARCHAR, product_id_param INT, required_quantity INT)
RETURNS BOOLEAN AS $$
DECLARE
    available_stock INT;
BEGIN
    -- Check stock based on product category
    IF category_param = 'seafood' THEN
        SELECT stock INTO available_stock FROM seafood WHERE product_id = product_id_param;
    ELSIF category_param = 'vegetables' THEN
        SELECT stock INTO available_stock FROM vegetables WHERE product_id = product_id_param;
    ELSIF category_param = 'meats' THEN
        SELECT stock INTO available_stock FROM meats WHERE product_id = product_id_param;
    ELSIF category_param = 'fruits' THEN
        SELECT stock INTO available_stock FROM fruits WHERE product_id = product_id_param;
    ELSE
        RAISE EXCEPTION 'Invalid product category: %', category_param;
    END IF;
    
    -- Check if product exists
    IF available_stock IS NULL THEN
        RAISE EXCEPTION 'Product not found: % in category %', product_id_param, category_param;
    END IF;
    
    RETURN available_stock >= required_quantity;
END;
$$ LANGUAGE plpgsql;

-- 2. Generic stock update function
CREATE OR REPLACE FUNCTION update_product_stock(category_param VARCHAR, product_id_param INT, quantity_change INT)
RETURNS BOOLEAN AS $$
DECLARE
    current_stock INT;
BEGIN
    -- Get current stock based on product category
    IF category_param = 'seafood' THEN
        SELECT stock INTO current_stock FROM seafood WHERE product_id = product_id_param;
    ELSIF category_param = 'vegetables' THEN
        SELECT stock INTO current_stock FROM vegetables WHERE product_id = product_id_param;
    ELSIF category_param = 'meats' THEN
        SELECT stock INTO current_stock FROM meats WHERE product_id = product_id_param;
    ELSIF category_param = 'fruits' THEN
        SELECT stock INTO current_stock FROM fruits WHERE product_id = product_id_param;
    ELSE
        RAISE EXCEPTION 'Invalid product category: %', category_param;
    END IF;
    
    -- Check if product exists
    IF current_stock IS NULL THEN
        RAISE EXCEPTION 'Product not found: % in category %', product_id_param, category_param;
    END IF;
    
    -- Check if stock is sufficient
    IF current_stock + quantity_change < 0 THEN
        RETURN FALSE;
    END IF;
    
    -- Update stock
    IF category_param = 'seafood' THEN
        UPDATE seafood SET stock = stock + quantity_change WHERE product_id = product_id_param;
    ELSIF category_param = 'vegetables' THEN
        UPDATE vegetables SET stock = stock + quantity_change WHERE product_id = product_id_param;
    ELSIF category_param = 'meats' THEN
        UPDATE meats SET stock = stock + quantity_change WHERE product_id = product_id_param;
    ELSIF category_param = 'fruits' THEN
        UPDATE fruits SET stock = stock + quantity_change WHERE product_id = product_id_param;
    END IF;
    
    RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

-- =========================
-- Updated trigger functions
-- =========================

-- 1. Automatically deduct inventory and balance when order is created
CREATE OR REPLACE FUNCTION trigger_process_order_creation()
RETURNS TRIGGER AS $$
DECLARE
    item RECORD;
    stock_updated BOOLEAN;
    balance_updated BOOLEAN;
BEGIN
    -- Check inventory for all order items
    FOR item IN 
        SELECT category, product_id, quantity 
        FROM order_items 
        WHERE order_id = NEW.order_id
    LOOP
        IF NOT check_product_stock(item.category, item.product_id, item.quantity) THEN
            RAISE EXCEPTION 'Insufficient stock for product_id: % in category: %', item.product_id, item.category;
        END IF;
    END LOOP;
    
    -- Check user balance
    IF NOT update_user_balance(NEW.user_id, -NEW.total_price) THEN
        RAISE EXCEPTION 'Insufficient balance for user_id: %', NEW.user_id;
    END IF;
    
    -- Deduct inventory
    FOR item IN 
        SELECT category, product_id, quantity 
        FROM order_items 
        WHERE order_id = NEW.order_id
    LOOP
        PERFORM update_product_stock(item.category, item.product_id, -item.quantity);
    END LOOP;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 2. Check inventory when adding cart items
CREATE OR REPLACE FUNCTION trigger_check_cart_stock_updated()
RETURNS TRIGGER AS $$
BEGIN
    -- Use generic inventory check function
    IF NOT check_product_stock(NEW.category, NEW.product_id, NEW.quantity) THEN
        RAISE EXCEPTION 'Insufficient stock for product_id: % in category: %', NEW.product_id, NEW.category;
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 3. Deduct inventory when cart is cleared
CREATE OR REPLACE FUNCTION trigger_cart_clear_inventory()
RETURNS TRIGGER AS $$
DECLARE
    item RECORD;
BEGIN
    -- Deduct inventory
    FOR item IN 
        SELECT category, product_id, quantity 
        FROM shopping_cart 
        WHERE user_id = OLD.user_id
    LOOP
        PERFORM update_product_stock(item.category, item.product_id, -item.quantity);
    END LOOP;
    
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

-- =========================
-- Delete old triggers and create new ones
-- =========================

-- Delete old triggers
DROP TRIGGER IF EXISTS trg_process_order ON orders;
DROP TRIGGER IF EXISTS trg_check_cart_stock ON shopping_cart;

-- Create new triggers
CREATE TRIGGER trg_process_order_creation
    AFTER INSERT ON orders
    FOR EACH ROW
    EXECUTE FUNCTION trigger_process_order_creation();

CREATE TRIGGER trg_check_cart_stock_updated
    BEFORE INSERT OR UPDATE ON shopping_cart
    FOR EACH ROW
    EXECUTE FUNCTION trigger_check_cart_stock_updated();

-- Note: Cart clearing trigger needs to be implemented at application level, as SQL DELETE trigger
-- cannot directly access deleted row data to deduct inventory

-- =========================
-- Create inventory management views
-- =========================

-- 1. All products inventory statistics view
CREATE OR REPLACE VIEW all_products_inventory AS
SELECT 
    'seafood' as category,
    product_id,
    name,
    price,
    stock,
    origin
FROM seafood
UNION ALL
SELECT 
    'vegetables' as category,
    product_id,
    name,
    price,
    stock,
    origin
FROM vegetables
UNION ALL
SELECT 
    'meats' as category,
    product_id,
    name,
    price,
    stock,
    origin
FROM meats
UNION ALL
SELECT 
    'fruits' as category,
    product_id,
    name,
    price,
    stock,
    origin
FROM fruits
ORDER BY category, product_id;

-- 2. Low stock products warning view
CREATE OR REPLACE VIEW low_stock_products AS
SELECT 
    category,
    product_id,
    name,
    price,
    stock,
    origin,
    CASE 
        WHEN stock = 0 THEN 'Out of Stock'
        WHEN stock <= 10 THEN 'Low Stock'
        ELSE 'Normal'
    END as stock_status
FROM all_products_inventory
WHERE stock <= 10
ORDER BY stock ASC, category, product_id;

-- 3. Sales statistics view (by product category)
CREATE OR REPLACE VIEW sales_by_category AS
SELECT 
    oi.category,
    COUNT(DISTINCT oi.order_id) as total_orders,
    COUNT(oi.item_id) as total_items,
    SUM(oi.quantity) as total_quantity_sold,
    SUM(oi.quantity * oi.price) as total_revenue,
    AVG(oi.quantity * oi.price) as avg_item_value
FROM order_items oi
JOIN orders o ON oi.order_id = o.order_id
GROUP BY oi.category
ORDER BY total_revenue DESC;
